import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

public class BdHelper
{
	Connection conn = null;
	Statement stat = null;

	public synchronized void salvarFeedback(String userId, String rua, String feedback)
	{
		abreConexao();
		try
		{
			stat.executeUpdate("insert into feedback values (\"" + userId + "\", \"" + rua + "\", \"" + feedback + "\");");
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
		fechaConexao();
	}
	
	public synchronized void abreConexao()
	{
		try
		{
			Class.forName("org.sqlite.JDBC");
			conn = DriverManager.getConnection("jdbc:sqlite:projeto_final.db");
			stat = conn.createStatement();
		}
		catch (Exception e)
		{
			//e.printStackTrace();
		}
	}

	public synchronized void fechaConexao()
	{
		try
		{
			stat.close();
			conn.close();
		}
		catch (SQLException e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
	}

	public synchronized void gravarBanco(String id, String texto, String autor, String data, String palavraChave, String classificacao)
	{
		try
		{

			abreConexao();
			// System.out.println("insert into tweet values (\"" + id + "\", \"" + texto + "\", \"" + autor + "\", \""+ data + "\", \"" + palavraChave+ "\");");
			stat.executeUpdate("insert into tweet values (\"" + id + "\", \"" + texto + "\", \"" + autor + "\", \"" + data + "\", \"" + palavraChave + "\", \"" + classificacao + "\");");

			fechaConexao();

		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
			//System.exit(-12);
		}
	}

	
	public synchronized long getLastId(String palavraChave)
	{

		try
		{
			abreConexao();
			ResultSet rs = stat.executeQuery("select id from tweet  where palavraChave = \"" + palavraChave + "\" order by id desc LIMIT 1");
			if (rs.next())
			{
				long id = rs.getLong("id");
				rs.close();
				fechaConexao();
				return id;
			}
			fechaConexao();
		}
		catch (SQLException e)
		{
			//e.printStackTrace();
			//System.exit(-12);
		}

		return -1;
	}

	public synchronized void limparBanco()
	{
		Calendar dataLimite = Calendar.getInstance();
		dataLimite.add(Calendar.HOUR, -1);
		ResultSet rs;
		Date createdAt; 
		ArrayList<String> id = new ArrayList<String>();

		try
		{
			abreConexao();
			rs = stat.executeQuery("select * from tweet;");
			while (rs.next())
			{
				SimpleDateFormat dateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy",  Locale.ENGLISH);
				createdAt = dateFormat.parse(rs.getString("data"));
				if(createdAt.getTime() > dataLimite.getTimeInMillis()) continue;
				id.add(rs.getString("id"));
				
			}
			for(String s: id)	stat.executeUpdate("delete from tweet where id = '" + s + "';");
			fechaConexao();
		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}

	}

	public synchronized boolean ruaconstaNoBanco(String rua)
	{
		try
		{
			abreConexao();
			ResultSet rs = stat.executeQuery("select * from ruas where nome = '" + rua + "'");
			//System.out.println("select * from ruas where nome = '" + rua + "'");
			if (rs.next())
			{
				fechaConexao();
				return true;
			}
			else
			{
				fechaConexao();
				return false;
			}
		}
		catch (Exception e)
		{
			//e.printStackTrace();
			//System.exit(-12);
		}
		return false;
	}

	public synchronized String getRuaNaoAbreviada(String abreviatura)
	{
		try
		{
			abreConexao();
			ResultSet rs = stat.executeQuery("select palavra from abreviaturas where abreviatura = '" + abreviatura + "'");
			if (rs.next())
			{
				String palavra = rs.getString("palavra");
				fechaConexao();
				return palavra;
			}
			else
			{
				fechaConexao();
				return abreviatura;
			}
		}
		catch (Exception e)
		{
		//	e.printStackTrace();
			//.exit(-12);
		}
		return abreviatura;
	}

	/*public synchronized void filtraTweets(String palavraChave)
	{
		try
		{
			abreConexao();
			stat.executeUpdate("delete from tweet where autor not in (select autor from autorConfiavel ) and palavraChave = '" + palavraChave + "'");
			fechaConexao();

		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}
	}*/

	public synchronized String getClassificacao(String palavra)
	{
		try
		{
			abreConexao();
			ResultSet rs = stat.executeQuery("select classificacao from dicionario where palavra = \"" + palavra + "\"");
			if (rs.next())
			{
				String classificacao = rs.getString("classificacao");
				fechaConexao();
				return classificacao;
			}

			else
			{
				fechaConexao();
				return "";
			}

		}
		catch (Exception e)
		{
			//e.printStackTrace();
		//	System.exit(-12);
		}
		return "";
	}

	public synchronized void setClassificacao(long id, String classificacao)
	{
		try
		{
		

			abreConexao();
			stat.executeUpdate("update tweet set classificacao = '" + classificacao + "' where id = '" + id + "'");
			fechaConexao();

		}
		catch (Exception e)
		{
			// TODO Auto-generated catch block
			//e.printStackTrace();
			//System.exit(-12);
		}
	}

	public synchronized ArrayList<Tweet_Id> getTweets(String palavraChave)
	{
		ArrayList<Tweet_Id> tweets = new ArrayList<Tweet_Id>();
		try
		{
			abreConexao();
			ResultSet rs = stat.executeQuery("select id, texto from tweet where palavraChave = '" + palavraChave + "'");
			while (rs.next())
			{
				tweets.add(new Tweet_Id(rs.getLong("id"), rs.getString("texto")));
			}
			fechaConexao();
			return tweets;

		}
		catch (Exception e)
		{
			//e.printStackTrace();
			//System.exit(-12);
		}
		return null;
	}

	public synchronized ArrayList<Classificacao_TempoRelativo> getTweetsRecentes(String palavraChave, boolean consultaConfiavel)
	{
		Calendar dataLimite = Calendar.getInstance();
		Calendar dataAtual = Calendar.getInstance();
		ResultSet rs;
		
		long minutos;
		long milisegundos;
		dataLimite.add(Calendar.MINUTE, -60);
		Date createdAt; 

		ArrayList<Classificacao_TempoRelativo> classificacoes = new ArrayList<Classificacao_TempoRelativo>();
		try
		{
			abreConexao();
			if(!consultaConfiavel)  rs = stat.executeQuery("select classificacao, data, texto from tweet where palavraChave = '" + palavraChave + "';");
			else                    rs = stat.executeQuery("select classificacao, data, texto from tweet where palavraChave = '" + palavraChave + "' and autor in (select autor from autorConfiavel);");  
			while (rs.next())
			{
				SimpleDateFormat dateFormat = new SimpleDateFormat("EEE MMM dd HH:mm:ss Z yyyy",  Locale.ENGLISH);
				createdAt = dateFormat.parse(rs.getString("data"));
				if(createdAt.getTime() < dataLimite.getTimeInMillis()) continue;
				milisegundos = dataAtual.getTimeInMillis() - createdAt.getTime();
				minutos = milisegundos/60000;
				classificacoes.add(new Classificacao_TempoRelativo(rs.getString("classificacao"), minutos));
			}
			fechaConexao();
			return classificacoes;

		}
		catch (Exception e)
		{
			//e.printStackTrace();
			//System.exit(-12);
		}
		return classificacoes;

	}

}
